
#### Table of Contents

1. Create BQ Table for test
2. PubSub Topic
3. Create a Dataflow
4. Manually Publish PubSub Message
5. Send Pub/Sub Message from C#
6. Authenticating as a service account
7. Conclusion

GCP Pub/Sub is managed messaging service provide by GCP, this blog demo how to
create Pub/Sub and use dataflow to subscribe data into BigQuery.

## Create BQ Table for test

First, need to create a BQ table, so we can store our message from Pub/Sub
we send, below is the JSON and BQ Table assume we want to store the
information.

`{&ldquo;id&rdquo;: 100, &ldquo;name&rdquo;:&rdquo;Smith James",
url:&rdquo;https://testsmithjames.com&rdquo;,&rdquo;age&rdquo;:30,&rdquo;price&rdquo;:300,&rdquo;desc&rdquo;:&rdquo;I
like sell my arts.&rdquo;}

## PubSub Topic

Here, we try to create a topic via gcloud command line,

To create a PubSub Topic

gcloud pubsub topics create test_topic_myname

In case, need to delete the topic, below command is how to delete a PubSub
Topic

gcloud pubsub topics delete test_topic_myname

## Create a Dataflow

Now, the following command example will create a dataflow job by using the
template PubSub to BigQuery and will insert the new record to the Big Query
table defined.

This command also expects the cloud bucket already exists.

```
gcloud dataflow jobs run ps-to-bq-test-topic-myname --gcs-location gs://dataflow-templates-us-central1/latest/PubSub_to_BigQuery --region us-central1 --staging-location gs://test-pubsub-name-topic/temp --parameters inputTopic=projects/test-dataflow-sql/topics/test_topic_myname,outputTableSpec=test-dataflow-sql:dataflow_sql_tutorial.test_pubsub_to_bq
```

## Manually Publish PubSub Message

Let's test the PubSub Dataflow to BigQuery, go to the GCP Pub/Sub page, and
paste the below Json, once click the publish button should see the new
record in BigQuery table.

```
{
  "id": 100,
  "name": "smith james",
  "url": "https://testsmithjames.com",
  "age": 30,
  "price": 300,
  "desc": "I like sell my arts"
}
```

## Send Pub/Sub Message from C#

Following is a sample code by using C# to send a message to the projecId and
topicId.

```
TopicName topicName = TopicName.FromProjectTopic(ProjectId, TopicId);

PublisherClient publisher = await PublisherClient.CreateAsync(topicName);

SamplePubSubModel sampleModel = new()
{
                id = 304,
                name = "my name",
                url = "https://test-url",
                age = 40,
                price = 300,
                desc = "some desc"
};

await publisher.PublishAsync(JsonConvert.SerializeObject(sampleModel));
```

Above C# code require the Google.Cloud.PubSub.V1, but also need to
authenticate then the code will successfully run.

## Authenticating as a service account

Now, just run the C# code above, and you'll see the exception which requires
providing the google credentials. There're a few ways to achieve this, what
we're doing here is trying to create a service account that has permission to
pub/sub message.

Once create a service account, make sure has the Role
Pub/Sub Publisher`.

Create a token and save the credential JSON file to the local, if you're using
windows, run the below command. Now, after following the below command, might
still see the credential error but, it might be fixed after restarting the
windows.

`set GOOGLE_APPLICATION_CREDENTIALS=key_path`

## Conclusion

Should be very easy to set up all processes, but might take some time on how
the google application credentials especially if you're using windows.


GCP Pub/Sub is managed messaging service provide by GCP, this blog demo how to create Pub/Sub and use dataflow to subscribe datainto BigQuery.

PubSub to BigQuery via Dataflow with GCP


#### Table of Contents

1. About Terraform with GCP
2. Preparation
3. Create terraform files
4. Create provider.tf file
5. Create service account
6. Create backend.tf file
7. Create schedule-job.tf
8. Start Deployment
9. Conclusion

## About Terraform with GCP

Terraform provide infrastructure automation to provision resources to many cloud provider, GCP is one of them, this article shows how to use Terraform to deploy a simple Big Query Schedule Job to the GCP Cloud.

## Preparation

## Create terraform files

### Create `provider.tf` file

Here start with the terraform provider, google credentials and the GCP project.

```
terraform {
  required_providers {
    google = {
      source = "hashicorp/google"
      version = "5.15.0"
    }
  }
}

provider "google" {
  credentials = file("{this is option but your can create GCP service account with BigQuery.job.create role}")
  project = "{your gcp project id}"
}
```

### Create service account

For the above credentials, below are few steps to create a service account has minimal role to perform below GCP operation.

- go to the GCP Roles page, create a new custom role with `bigquery.job.create` permission.
- go to the IAM service account page, create new service account and then assign the previous custom role.
- once the service account created, go to the KEYS tab and click `ADD KEY` to create new JSON key and save locally so above credentials can lookup

### Create `backend.tf` file

Here, we're using the GCP cloud bucket to store the terraform state file, this is similar to share the code within the team so we can deploy same infrastructure no matter who runs it.

```
terraform {
 backend "gcs" {
   bucket  = "{create a GCP cloud bucket}"
   prefix = "test"
 }
}
```

### Create `schedule-job.tf`

Now, finally here come to the main code we're trying to write a simple query and deploy to GCP BQ Schedule job and runs every sunday.

```
resource "google_bigquery_data_transfer_config" "job" {
    display_name = "test-query"
    project = "{your gcp project}"
    location = "us"
    data_source_id = "scheduled_query"
    schedule = "first sunday of quarter 00:00"
    params = {
      query = "select 1 as ID;"
    }
}
```

### Start Deployment

First, go to the folder has above terraform files, run this command to initialize terraform from gcp cloud bucket to download state files.

```
terraform init
```

This command will start a new workspace with `test`, if not exist then will save the state to the cloud bucket.

```
terraform workspace new test
```

Terraform command to run the plan, to check what resources will generate to the GCP.

```
terraform plan
```

If you confirmed the resource, run this command to deploy the changes to the GCP.

```
terraform apply
```

## Conclusion

Use the terraform is great to deploy infrastructure resource to the GCP for productions.


This article demonstrate how to terraform to deploy GCP Schedule JOB

Category BigQuery

PubSub to BigQuery via Dataflow with GCP

How to Create GCP Schedule Job with Terraform